USE BishoyPhoto
GO

---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
-- Create Category Table and establish data
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

CREATE TABLE [dbo].[Category] (
	[ID] int NOT NULL CONSTRAINT PK_Category PRIMARY KEY,
	[Name] varchar(50) NOT NULL CONSTRAINT IX_Catefory_Name UNIQUE,
	[MainGalleryID] int NULL CONSTRAINT FK_Category_MainGallery FOREIGN KEY REFERENCES [dbo].[Gallery]([ID]),
	[Attributes] text NULL
)
GO


INSERT INTO [dbo].[Category] (ID, Name) VALUES (0, 'Main')
INSERT INTO [dbo].[Category] (ID, Name) VALUES (1, 'Weddings')
INSERT INTO [dbo].[Category] (ID, Name) VALUES (2, 'Sessions')
INSERT INTO [dbo].[Category] (ID, Name) VALUES (3, 'Kids')
INSERT INTO [dbo].[Category] (ID, Name) VALUES (4, 'Events')
INSERT INTO [dbo].[Category] (ID, Name) VALUES (5, 'Schools')
INSERT INTO [dbo].[Category] (ID, Name) VALUES (6, 'Products')
GO

---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
-- Update Gallery Table to refer to categories
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

DELETE [dbo].[Photo]
GO

DELETE [dbo].[Gallery]
GO

ALTER TABLE [dbo].[Gallery] ADD [CategoryID] int NOT NULL
GO

ALTER TABLE [dbo].[Gallery] ADD CONSTRAINT FK_Gallery_Category FOREIGN KEY ([CategoryID]) REFERENCES [dbo].[Category] (ID)
GO

---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
-- Establish main galleries
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

SET IDENTITY_INSERT [dbo].[Gallery] ON
GO

INSERT INTO [dbo].[Gallery]([ID],[Name],[CategoryID],[Order],[Password],[Description],[ArchiveURL]) VALUES(1,N'Weddings {Main}',0,1,NULL,N'',NULL)
INSERT INTO [dbo].[Gallery]([ID],[Name],[CategoryID],[Order],[Password],[Description],[ArchiveURL]) VALUES(2,N'Sessions {Main}',0,2,NULL,N'',NULL)
INSERT INTO [dbo].[Gallery]([ID],[Name],[CategoryID],[Order],[Password],[Description],[ArchiveURL]) VALUES(3,N'Kids {Main}',0,3,NULL,N'',NULL)
INSERT INTO [dbo].[Gallery]([ID],[Name],[CategoryID],[Order],[Password],[Description],[ArchiveURL]) VALUES(4,N'Events {Main}',0,4,NULL,N'',NULL)
INSERT INTO [dbo].[Gallery]([ID],[Name],[CategoryID],[Order],[Password],[Description],[ArchiveURL]) VALUES(5,N'Schools {Main}',0,5,NULL,N'',NULL)
INSERT INTO [dbo].[Gallery]([ID],[Name],[CategoryID],[Order],[Password],[Description],[ArchiveURL]) VALUES(6,N'Products {Main}',0,6,NULL,N'',NULL)
GO

SET IDENTITY_INSERT [dbo].[Gallery] OFF
GO

-- Link main galleries to categories

UPDATE [dbo].[Category] SET MainGalleryID = ID WHERE ID <> 0
GO

---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
-- Update procedures
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

CREATE PROC [dbo].[GetCategories]
AS
	SELECT [ID], [Name], [MainGalleryID], [Attributes] FROM [dbo].[Category]
GO


ALTER PROC [dbo].[AddGallery] (@Name AS nvarchar(200), @CategoryID int, @Password AS nvarchar(8), @Description AS nvarchar(500), @ArchiveURL AS varchar(256))
AS
DECLARE @NextOrder as int

SET @NextOrder = ISNULL((SELECT MAX([Order]) FROM [dbo].[Gallery] WHERE [CategoryID] = @CategoryID), 0) + 1

INSERT INTO [dbo].[Gallery]
           ([Name]
		   ,[CategoryID]
           ,[Order]
           ,[Password]
           ,[Description]
           ,[ArchiveURL])
     VALUES
           (@Name
		   ,@CategoryID
           ,@NextOrder
           ,@Password
           ,@Description
           ,@ArchiveURL)
GO

DROP PROC [dbo].[GetPublicGalleries]
GO

CREATE PROC [dbo].[GetGalleries] (@CategoryID int) AS
SELECT [dbo].[Gallery].[ID]
      ,[Name]
      ,[dbo].[Gallery].[Description]
      ,MAX([Location]) AS [CoverImage]
  FROM [dbo].[Gallery]
  LEFT JOIN [dbo].[Photo] ON [dbo].[Gallery].[ID] = [dbo].[Photo].[GalleryID]
  AND [dbo].[Photo].[Order] = 1
  WHERE [Password] IS NULL AND [CategoryID] = @CategoryID
  GROUP BY [dbo].[Gallery].[ID], [dbo].[Gallery].[Order], [Name],[dbo].[Gallery].[Description]
  ORDER BY [dbo].[Gallery].[Order]
GO

CREATE PROC [dbo].[GetGalleryInfo] (@GalleryID int) AS
SELECT [dbo].[Gallery].[ID]
      ,[Name]
      ,[dbo].[Gallery].[Description]
      ,MAX([Location]) AS [CoverImage]
	  ,[CategoryID]
  FROM [dbo].[Gallery]
  LEFT JOIN [dbo].[Photo] ON [dbo].[Gallery].[ID] = [dbo].[Photo].[GalleryID]
  AND [dbo].[Photo].[Order] = 1
  WHERE [dbo].[Gallery].[ID] = @GalleryID
  GROUP BY [dbo].[Gallery].[ID], [dbo].[Gallery].[Order], [Name],[dbo].[Gallery].[Description], [CategoryID]
  ORDER BY [dbo].[Gallery].[Order]
GO